package com.example.demo.zb;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.UUID;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo.controller.DataController;
import com.example.demo.model.*;
import com.example.demo.utils.*;
import com.example.demo.zb.dao.ChannelDO;
import com.example.demo.zb.dao.ChannelDOMapper;
import com.google.common.base.Splitter;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.EnumUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author linjingze
 * @date 2023/12/15 8:22 下午
 */
@Service
public class ExcelReader {

    private static String zjgbs = "宁波芝加哥卡派(包税)";
    private static String zjgbbs = "宁波芝加哥卡派(不包税)";

    private static String xsdbs = "宁波休斯顿卡派(包税)";
    private static String xsdbbs = "宁波休斯顿卡派(不包税)";

    private static String mdbbs = "宁波美东卡派(不包税)";
    private static String mdbs = "宁波美东卡派(包税)";


    public static List<String> XSD_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            xsdbs,
            xsdbbs
    );
    public static List<String> ZJG_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            zjgbbs,
            zjgbs
    );
    public static List<String> NY_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            mdbbs,
            mdbs
    );



    public static List<String> UN_NB_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            "青岛美东海外仓卡派-不包税",
            "上海美东卡派包税",
            "休斯顿卡派(包税)",
            "芝加哥卡派(包税)",
            "纽约卡派(包税)"
    );

    public static List<String> SRDZ_LIST = Arrays.asList(
            "CA90",
            "CA91",
            "CA92"
    );

    private static class W {
        private String t;

        private List<String> c;

        public W(String t, List<String> c) {
            this.t = t;
            this.c = c;
        }

        public String getT() {
            return t;
        }

        public List<String> getC() {
            return c;
        }
    }

    public static Map<String, W> DEST_MAP = new HashMap<>();

    {
        DEST_MAP.put("宁波海外仓", new W("LAX", jiufangCangkuList));
        DEST_MAP.put("宁波OA直送专线(包税)",new W( "OAK", Arrays.asList("SCK4", "SMF3")));
        DEST_MAP.put("宁波OA直送专线(不包税)", new W("OAK", Arrays.asList("SCK4", "SMF3")));

    }


    public static List<String> QD_LIST = Arrays.asList(
            "深圳销售+渠道",
            "宁波销售"
    );


   static String FBA_PATTERN = "^[a-zA-Z]{3}\\d$";

   static Pattern FBA_P = Pattern.compile(FBA_PATTERN);

    public static void yuebao(File file, String qd) {
        List<CargoData> oldDataList = EasyExcel.read(file).head(CargoData.class).sheet().doReadSync();
        List<CargoData> dataList = oldDataList.stream().filter(s -> "LCL".equals(s.getCType()))
                .filter(s -> StringUtils.isNotBlank(s.getInboundChannelName()) && !s.getInboundChannelName().contains("整柜"))
                .toList();
        if (StringUtils.isNotBlank(qd)) {
            List<String> qdList = Splitter.on(",").splitToList(qd);
            dataList = dataList.stream().filter(s -> {
                String inboundChannelName = s.getInboundChannelName();
                for (String q : qdList) {
                    if (inboundChannelName.contains(q)) {
                        return true;
                    }
                }
                return false;
            }).collect(Collectors.toList());
        }
        //1.创建一个工作薄
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        XSSFCellStyle cellStyle = leftStyle(xssfWorkbook);
        String opFileName = file.getParent() + File.separator + "客户出货统计" + UUID.fastUUID() + ".xlsx";
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(opFileName);
            nandukehuchuhuoliang(xssfWorkbook, cellStyle, dataList);
            xssfWorkbook.write(fileOutputStream);
            OpenUtils.open(file.getParent());
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            IOUtils.closeQuietly(fileOutputStream);
        }

    }

    public static void nandukehuchuhuoliang(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        DataController.map.clear();
        DataController.nameList.clear();
        int quarter = 4;
        int month = 3;
        SimpleDateFormat sdf = new SimpleDateFormat("MM");
        Map<String, List<CargoData>> dateMap = dataList.stream()
                .collect(Collectors.groupingBy(c -> sdf.format(c.getDateTime())));
        int currentMonth = 0;
        for (int i = 1; i <= quarter; i++) {
            //2.创建工作表
            XSSFSheet sheet = xssfWorkbook.createSheet(i + "季度");
            List<BaseRowData> rowDataList = new ArrayList<>();
            for (int m = 0; m < month; m++) {
                currentMonth++;
                String monthStr = String.format("%02d", currentMonth);
                List<CargoData> monthDataList = dateMap.getOrDefault(monthStr, new ArrayList<>());
                int dc = m * 5 + 1;
                BaseRowData e = new BaseRowData(currentMonth + "月客户货量统计表(柱状图详见:http://localhost:8099/customer/page?customer=)", dc, 2).setCellStyle(centerStyle(xssfWorkbook));
                rowDataList.add(e);
                rowDataList.add(new BaseRowData("客户名称", dc).setStartRow(2));
                rowDataList.add(new BaseRowData("业务员", dc+1).setStartRow(2));
                rowDataList.add(new BaseRowData("求和项:入库体积", dc+2).setStartRow(2));
                List<CustomerRowData> customerRowDataList = getCustomerRowData(monthDataList);
                int c = 3;
                for (CustomerRowData customerRowData : customerRowDataList) {
                    String customerName = customerRowData.getCustomerName();
                    DataController.nameList.add(customerName);
                    rowDataList.add(new BaseRowData(customerName, dc).setStartRow(c));
                    rowDataList.add(new BaseRowData(customerRowData.getSales(), dc+1).setStartRow(c));
                    BigDecimal volume = customerRowData.getVolume();
                    rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(volume), dc+2).setStartRow(c));
                    Map<Integer, BigDecimal> vMap = DataController.map.computeIfAbsent(customerName, v -> new HashMap<>());
                    vMap.put(currentMonth, volume);
                    c++;
                }
            }
            ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
        }
    }



    public static void newkWeelyReport(File file) {
        List<CargoData> oldDataList = EasyExcel.read(file).head(CargoData.class).sheet().doReadSync();
        List<CargoData> dataList = oldDataList.stream().filter(s -> "LCL".equals(s.getCType()))
                .filter(s -> StringUtils.isNotBlank(s.getInboundChannelName()) && !s.getInboundChannelName().contains("整柜"))
                .toList();
        //1.创建一个工作薄
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        XSSFCellStyle cellStyle = leftStyle(xssfWorkbook);
        String opFileName = file.getParent() + File.separator + "周报.xlsx";
        FileOutputStream fileOutputStream = null;
        try {
            String date = getDate(oldDataList);
            fileOutputStream = new FileOutputStream(opFileName);
            feiningbo(xssfWorkbook, cellStyle, dataList, date);
            gequdao(xssfWorkbook, cellStyle, dataList, date);
            mxTop15(xssfWorkbook, cellStyle, dataList, date);
            zpmdg(xssfWorkbook, cellStyle, dataList, date);
            xiaoshougecang(xssfWorkbook, cellStyle, dataList, date);
            xssfWorkbook.write(fileOutputStream);
            OpenUtils.open(file.getParent());
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            IOUtils.closeQuietly(fileOutputStream);
        }

    }

    public static void newZhoubao(InputStream file, String outPath) {
        List<CargoData> oldDataList = EasyExcel.read(file).head(CargoData.class).sheet().doReadSync();
        List<CargoData> dataList = oldDataList.stream().filter(s -> "LCL".equals(s.getCType()))
                .filter(s -> StringUtils.isNotBlank(s.getInboundChannelName()) && !s.getInboundChannelName().contains("整柜"))
                .toList();
        //1.创建一个工作薄
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        XSSFCellStyle cellStyle = leftStyle(xssfWorkbook);
        String opFileName = outPath + File.separator + "周报.xlsx";
        FileOutputStream fileOutputStream = null;
        try {
            String date = getDate(oldDataList);
            fileOutputStream = new FileOutputStream(opFileName);
            feiningbo(xssfWorkbook, cellStyle, dataList, date);
            gequdao(xssfWorkbook, cellStyle, dataList, date);
            mxTop15(xssfWorkbook, cellStyle, dataList, date);
            zpmdg(xssfWorkbook, cellStyle, dataList, date);
            xiaoshougecang(xssfWorkbook, cellStyle, dataList, date);
            xssfWorkbook.write(fileOutputStream);
            OpenUtils.open(outPath);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            IOUtils.closeQuietly(fileOutputStream);
        }

    }
    private static XSSFCellStyle leftStyle(XSSFWorkbook xssfWorkbook) {
        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
        // 设置边框样式
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFFont font = xssfWorkbook.createFont();
        // 字体大小:字号
        font.setFontHeightInPoints((short) 8);
        font.setFontName("微软雅黑");
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        return cellStyle;
    }

    private static CellStyle centerStyle(XSSFWorkbook xssfWorkbook) {
        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
        // 设置边框样式
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFFont font = xssfWorkbook.createFont();
        // 字体大小:字号
        font.setFontHeightInPoints((short) 8);
        font.setFontName("微软雅黑");
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        return cellStyle;
    }

    public static void reader(File file) {
        List<CargoData> oldDataList = EasyExcel.read(file).head(CargoData.class).sheet().doReadSync();
        List<CargoData> dataList = oldDataList.stream().filter(s -> "LCL".equals(s.getCType())).toList();
        //1.创建一个工作薄
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        XSSFCellStyle cellStyle = leftStyle(xssfWorkbook);
        XSSFFont font = xssfWorkbook.createFont();
        // 字体大小:字号
        font.setFontHeightInPoints((short) 8);
        font.setFontName("微软雅黑");
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        String opFileName = file.getParent() + File.separator + "周报" + System.currentTimeMillis() + ".xlsx";
        FileOutputStream fileOutputStream = null;
        try {
            String date = getDate(oldDataList);
            fileOutputStream = new FileOutputStream(opFileName);
            w1(xssfWorkbook, cellStyle, dataList, date);
            w2(xssfWorkbook, cellStyle, dataList, date);
            w3(xssfWorkbook, cellStyle, dataList, date);
            w4(xssfWorkbook, cellStyle, dataList, date);
            w5(xssfWorkbook, cellStyle, dataList, date);
            //w6(xssfWorkbook, cellStyle, dataList);
            //w7(xssfWorkbook, cellStyle, dataList);
            xssfWorkbook.write(fileOutputStream);
            OpenUtils.open(opFileName);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            IOUtils.closeQuietly(fileOutputStream);
        }

    }

    public static void main(String[] args) throws IOException {
        String fileName = "/Users/linjingze/Downloads/程乔玉工作/货量统计.xlsx";
        //String fileName = "/Users/linjingze/Downloads/程乔玉工作/12.xlsx";
        reader(new File(fileName));
    }


    public static void w1(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("美东自拼TOP10客户货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 美东自拼TOP10客户货量统计表", 1, 2).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("客户名称", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", 3).setStartRow(2));

        List<CustomerRowData> customerRowDataList = getCustomerRowData(dataList);
        int c = 3;
        int end = Math.min(customerRowDataList.size(), 10);
        //int end = customerRowDataList.size();
        for (int i = 0; i < end; i++) {
            CustomerRowData customerRowData = customerRowDataList.get(i);
            rowDataList.add(new BaseRowData(customerRowData.getCustomerName(),1).setStartRow(c));
            rowDataList.add(new BaseRowData(customerRowData.getSales(),2).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(customerRowData.getVolume()), 3).setStartRow(c));
            c++;
        }
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    public static void mxTop15(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {



        //2.创建工作表
        int top = 20;
        XSSFSheet sheet = xssfWorkbook.createSheet("美线自拼TOP" + top + "客户货量统计表");

        int dc = 1;
        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 美线自拼TOP" + top + "客户货量统计表", 1, 3).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("客户名称", dc).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", dc+1).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", dc+2).setStartRow(2));

        List<CustomerRowData> customerRowDataList = getCustomerRowData(dataList);
        BigDecimal allV = customerRowDataList.stream().map(CustomerRowData::getVolume).collect(BigDecimalCollector.toSum());
        int c = 3;
        int end = Math.min(customerRowDataList.size(), top);
        //int end = customerRowDataList.size();
        BigDecimal top10Sum = BigDecimal.ZERO;
        for (int i = 0; i < end; i++) {
            CustomerRowData customerRowData = customerRowDataList.get(i);
            rowDataList.add(new BaseRowData(customerRowData.getCustomerName(), dc).setStartRow(c));
            rowDataList.add(new BaseRowData(customerRowData.getSales(), dc+1).setStartRow(c));
            BigDecimal volume = customerRowData.getVolume();
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(volume), dc+2).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.mulKeep2Scale(new BigDecimal(100), BigDecimalUtils.divKeep8Scale(volume, allV))) + "%", dc+3).setStartRow(c));
            top10Sum = BigDecimalUtils.add(top10Sum, volume);
            c++;
        }
        String zzb = BigDecimalUtils.toPlainString(BigDecimalUtils.mulKeep2Scale(new BigDecimal(100), BigDecimalUtils.divKeep8Scale(top10Sum, allV)));
        rowDataList.add(new BaseRowData(StrUtil.format("TOP" + top + "总货量占比({}%)", zzb), dc+3).setStartRow(2));


        //美东/萨瓦纳/芝加哥流向的：TOP10的客户货量统计
        //美东NYC/萨瓦纳SAV/芝加哥CHI流向的：TOP10的客户货量统计
        c = 3;
        top = 15;
        dc = 6;
        rowDataList.add(new BaseRowData(date + " 美东/萨瓦纳/芝加哥自拼TOP" + top + "客户货量统计表", dc, 3).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("客户名称", dc).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", dc+1).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", dc+2).setStartRow(2));

        customerRowDataList = getCustomerRowDataByChannel(dataList, Arrays.asList(ChannelEnum.NYC.name(), ChannelEnum.SAV.name(), ChannelEnum.CHI.name()));
        allV = customerRowDataList.stream().map(CustomerRowData::getVolume).collect(BigDecimalCollector.toSum());
        end = Math.min(customerRowDataList.size(), top);
        //int end = customerRowDataList.size();
        top10Sum = BigDecimal.ZERO;
        for (int i = 0; i < end; i++) {
            CustomerRowData customerRowData = customerRowDataList.get(i);
            rowDataList.add(new BaseRowData(customerRowData.getCustomerName(), dc).setStartRow(c));
            rowDataList.add(new BaseRowData(customerRowData.getSales(), dc+1).setStartRow(c));
            BigDecimal volume = customerRowData.getVolume();
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(volume), dc+2).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.mulKeep2Scale(new BigDecimal(100), BigDecimalUtils.divKeep8Scale(volume, allV))) + "%", dc+3).setStartRow(c));
            top10Sum = BigDecimalUtils.add(top10Sum, volume);
            c++;
        }
        zzb = BigDecimalUtils.toPlainString(BigDecimalUtils.mulKeep2Scale(new BigDecimal(100), BigDecimalUtils.divKeep8Scale(top10Sum, allV)));
        rowDataList.add(new BaseRowData(StrUtil.format("TOP" + top + "总货量占比({}%)", zzb), dc+3).setStartRow(2));


        //美西/奥克兰/西雅图/休斯顿流向：TOP10的客户货量统计
        //美西LAX/奥克兰OAK/西雅图SEA/休斯顿HOU流向：TOP10的客户货量统计
        c = 3;
        dc = 11;
        rowDataList.add(new BaseRowData(date + " 美西/奥克兰/西雅图/休斯顿自拼TOP" + top + "客户货量统计表", dc, 3).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("客户名称", dc).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", dc+1).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", dc+2).setStartRow(2));

        customerRowDataList = getCustomerRowDataByChannel(dataList, Arrays.asList(ChannelEnum.LAX.name(), ChannelEnum.OAK.name(), ChannelEnum.SEA.name(),ChannelEnum.HOU.name()));
        allV = customerRowDataList.stream().map(CustomerRowData::getVolume).collect(BigDecimalCollector.toSum());
        end = Math.min(customerRowDataList.size(), top);
        //int end = customerRowDataList.size();
        top10Sum = BigDecimal.ZERO;
        for (int i = 0; i < end; i++) {
            CustomerRowData customerRowData = customerRowDataList.get(i);
            rowDataList.add(new BaseRowData(customerRowData.getCustomerName(), dc).setStartRow(c));
            rowDataList.add(new BaseRowData(customerRowData.getSales(), dc+1).setStartRow(c));
            BigDecimal volume = customerRowData.getVolume();
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(volume), dc+2).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.mulKeep2Scale(new BigDecimal(100), BigDecimalUtils.divKeep8Scale(volume, allV))) + "%", dc+3).setStartRow(c));
            top10Sum = BigDecimalUtils.add(top10Sum, volume);
            c++;
        }
        zzb = BigDecimalUtils.toPlainString(BigDecimalUtils.mulKeep2Scale(new BigDecimal(100), BigDecimalUtils.divKeep8Scale(top10Sum, allV)));
        rowDataList.add(new BaseRowData(StrUtil.format("TOP" + top + "总货量占比({}%)", zzb), dc+3).setStartRow(2));


        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    public static void w7(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        XSSFSheet sheet = xssfWorkbook.createSheet("美东自拼客户货量统计表");
        List<BaseRowData> rowDataList = new ArrayList<>();
        Map<String, List<CargoData>> customerMap = dataList.stream().collect(Collectors.groupingBy(CargoData::getCustomerName));
        Map<String, String> customerSaleMap = new HashMap<>();
        CellStyle centerStyle = centerStyle(xssfWorkbook);
        for (Map.Entry<String, List<CargoData>> e : customerMap.entrySet()) {
            HashSet<String> nameSet = new HashSet<>();
            for (CargoData cargoData : e.getValue()) {
                if (StringUtils.isNotBlank(cargoData.getService())) {
                    nameSet.add(cargoData.getService());
                }
            }
            customerSaleMap.put(e.getKey(), String.join("/", nameSet));
        }
        w7(rowDataList, "休斯顿HOU", 0, dataList, getHOUList(), customerSaleMap, centerStyle);
        w7(rowDataList, "芝加哥CHI", 6, dataList, getCHIList(), customerSaleMap,centerStyle);
        w7(rowDataList, "纽约NYC", 12, dataList, getNYCList(), customerSaleMap,centerStyle);
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    private static List<String> getNYCList() {
        return getQdList(ChannelEnum.NYC);
    }

    private static List<String> getCHIList() {
        return getQdList(ChannelEnum.CHI);
    }

    private static List<String> getHOUList() {
        return getQdList(ChannelEnum.HOU);
    }



    private static List<String> getQdList(ChannelEnum channelEnum) {
        QueryWrapper<ChannelDO> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("type", channelEnum.name());
        List<ChannelDO> channelDOS = SpringContextUtils.getBean(ChannelDOMapper.class).selectList(queryWrapper);
        return channelDOS.stream().map(ChannelDO::getName).filter(StringUtils::isNotBlank).toList();
    }

    private static List<String> getSupportChannelList() {
        QueryWrapper<ChannelDO> queryWrapper = new QueryWrapper<>();
        List<ChannelDO> channelDOS = SpringContextUtils.getBean(ChannelDOMapper.class).selectList(queryWrapper);
        return channelDOS.stream().sorted((o1, o2) -> {
            String type1 = o1.getType();
            String type2 = o2.getType();
            if (StringUtils.equals(type1, type2)) {
                return StringUtils.compare(o1.getName(), o2.getName());
            }
            ChannelEnum channelEnum1 = EnumUtils.getEnum(ChannelEnum.class, type1,ChannelEnum.DEFAULT);
            ChannelEnum channelEnum2 = EnumUtils.getEnum(ChannelEnum.class, type2,ChannelEnum.DEFAULT);
            return Integer.compare(channelEnum1.sort(), channelEnum2.sort());
        }).map(ChannelDO::getName).filter(StringUtils::isNotBlank).toList();
    }

    private static List<String> getSupportChannelList(List<String> channelList) {
        QueryWrapper<ChannelDO> queryWrapper = new QueryWrapper<>();
        List<ChannelDO> channelDOS = SpringContextUtils.getBean(ChannelDOMapper.class).selectList(queryWrapper);
        return channelDOS.stream().filter(c -> channelList.contains(c.getType())).sorted((o1, o2) -> {
            String type1 = o1.getType();
            String type2 = o2.getType();
            if (StringUtils.equals(type1, type2)) {
                return StringUtils.compare(o1.getName(), o2.getName());
            }
            ChannelEnum channelEnum1 = EnumUtils.getEnum(ChannelEnum.class, type1,ChannelEnum.DEFAULT);
            ChannelEnum channelEnum2 = EnumUtils.getEnum(ChannelEnum.class, type2,ChannelEnum.DEFAULT);
            return Integer.compare(channelEnum1.sort(), channelEnum2.sort());
        }).map(ChannelDO::getName).filter(StringUtils::isNotBlank).toList();
    }
    private static List<String> getPCSupportChannelList() {
        QueryWrapper<ChannelDO> queryWrapper = new QueryWrapper<>();
        List<ChannelDO> channelDOS = SpringContextUtils.getBean(ChannelDOMapper.class).selectList(queryWrapper);
        return channelDOS.stream().filter(ChannelDO::getIsOrdinaryVessel).sorted((o1, o2) -> {
            String type1 = o1.getType();
            String type2 = o2.getType();
            if (StringUtils.equals(type1, type2)) {
                return StringUtils.compare(o1.getName(), o2.getName());
            }
            ChannelEnum channelEnum1 = EnumUtils.getEnum(ChannelEnum.class, type1,ChannelEnum.DEFAULT);
            ChannelEnum channelEnum2 = EnumUtils.getEnum(ChannelEnum.class, type2,ChannelEnum.DEFAULT);
            return Integer.compare(channelEnum1.sort(), channelEnum2.sort());
        }).map(ChannelDO::getName).filter(StringUtils::isNotBlank).toList();
    }
    private static List<ChannelDO> getPCChannelList() {
        QueryWrapper<ChannelDO> queryWrapper = new QueryWrapper<>();
        List<ChannelDO> channelDOS = SpringContextUtils.getBean(ChannelDOMapper.class).selectList(queryWrapper);
        return channelDOS.stream().filter(ChannelDO::getIsOrdinaryVessel).sorted((o1, o2) -> {
            String type1 = o1.getType();
            String type2 = o2.getType();
            if (StringUtils.equals(type1, type2)) {
                return StringUtils.compare(o1.getName(), o2.getName());
            }
            ChannelEnum channelEnum1 = EnumUtils.getEnum(ChannelEnum.class, type1, ChannelEnum.DEFAULT);
            ChannelEnum channelEnum2 = EnumUtils.getEnum(ChannelEnum.class, type2, ChannelEnum.DEFAULT);
            return Integer.compare(channelEnum1.sort(), channelEnum2.sort());
        }).collect(Collectors.toList());
    }

    public static void w7(List<BaseRowData> rowDataList,
                          String title,
                          int startCol,
                          List<CargoData> dataList,
                          List<String> filterList,
                          Map<String, String> customerSaleMap,
                          CellStyle centerStyle) {

        //2.创建工作表

        rowDataList.add(new BaseRowData(title, startCol + 1, 4).setCellStyle(centerStyle));
        rowDataList.add(new BaseRowData("客户名称", startCol + 1).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", startCol + 2).setStartRow(2));
        rowDataList.add(new BaseRowData("客服/商务", startCol + 3).setStartRow(2));
        rowDataList.add(new BaseRowData("国内分区", startCol + 4).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", startCol + 5).setStartRow(2));

        List<CustomerRowData> customerRowDataList = getCustomerRowData(dataList, filterList);
        int c = 3;
        //int end = Math.min(customerRowDataList.size(), 10);
        int end = customerRowDataList.size();
        for (int i = 0; i < end; i++) {
            CustomerRowData customerRowData = customerRowDataList.get(i);
            rowDataList.add(new BaseRowData(customerRowData.getCustomerName(), startCol + 1).setStartRow(c));
            rowDataList.add(new BaseRowData(customerRowData.getSales(), startCol + 2).setStartRow(c));
            rowDataList.add(new BaseRowData(customerSaleMap.getOrDefault(customerRowData.getCustomerName(), ""), startCol + 3).setStartRow(c));
            rowDataList.add(new BaseRowData(customerRowData.getArea(), startCol + 4).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(customerRowData.getVolume()), startCol + 5).setStartRow(c));
            c++;
        }
    }

    private static List<CustomerRowData> getCustomerRowData(List<CargoData> dataList) {
        List<String> supportChannelList = getSupportChannelList();
        return dataList.stream().filter(s ->
                        {
                            String inboundChannelName = s.getInboundChannelName();
                            return supportChannelList.contains(inboundChannelName);
                        }
                )
                .collect(Collectors.groupingBy(s -> s.getCustomerName() + "," + s.getSales(), Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())))
                .entrySet().stream().map(e -> {
                    List<String> keyList = Splitter.on(",").splitToList(e.getKey());
                    return new CustomerRowData(keyList.get(0), keyList.get(1), e.getValue());
                }).sorted().collect(Collectors.toList());
    }

    private static List<CustomerRowData> getCustomerRowDataByChannel(List<CargoData> dataList, List<String> channelList) {
        List<String> supportChannelList = getSupportChannelList(channelList);
        return dataList.stream().filter(s ->
                        {
                            String inboundChannelName = s.getInboundChannelName();
                            return supportChannelList.contains(inboundChannelName);
                        }
                )
                .collect(Collectors.groupingBy(s -> s.getCustomerName() + "," + s.getSales(), Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())))
                .entrySet().stream().map(e -> {
                    List<String> keyList = Splitter.on(",").splitToList(e.getKey());
                    return new CustomerRowData(keyList.get(0), keyList.get(1), e.getValue());
                }).sorted().collect(Collectors.toList());
    }

    private static List<CustomerRowData> getCustomerRowData(List<CargoData> dataList, List<String> filterList) {
        Map<String, BigDecimal> areaVolMap = dataList.stream().filter(s -> filterList.contains(s.getInboundChannelName()))
                .filter(s -> StringUtils.isNotBlank(s.getArea()))
                .collect(Collectors.groupingBy(CargoData::getArea, Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())));

        return dataList.stream().filter(s -> filterList.contains(s.getInboundChannelName()))
                .collect(Collectors.groupingBy(s -> s.getCustomerName() + "," + StringUtils.defaultString(s.getArea())))
                .entrySet().stream().map(e -> {
                    List<CargoData> value = e.getValue();
                    BigDecimal volumeAll = value.stream().map(CargoData::getVolume).collect(BigDecimalCollector.toSum());
                    List<String> keyList = Splitter.on(",").splitToList(e.getKey());
                    String sales = value.stream().map(CargoData::getSales).filter(StringUtils::isNotBlank).distinct().collect(Collectors.joining(","));
                    CustomerRowData customerRowData = new CustomerRowData(keyList.get(0), sales, volumeAll);
                    //String areas = value.stream().map(CargoData::getArea).filter(StringUtils::isNotBlank).distinct().collect(Collectors.joining(","));
                    String area = StringUtils.defaultString(keyList.get(1));
                    customerRowData.setArea(area);
                    customerRowData.setAllVolume(areaVolMap.getOrDefault(area, BigDecimal.ZERO));
                    return customerRowData;
                }).sorted().collect(Collectors.toList());
    }


    public static void w2(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("美线各渠道货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 美线各渠道货量统计表", 1, 3).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("入库渠道", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("总计（CBM）", 4).setStartRow(2));
        List<String> supportChannelList = getSupportChannelList();
        Map<String, BigDecimal> map = dataList.stream().filter(s -> supportChannelList.contains(s.getInboundChannelName()))
                .collect(Collectors.groupingBy(CargoData::getInboundChannelName, Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())));

        int c = 3;
        int size = supportChannelList.size();
        rowDataList.add(new BaseRowData("普船", 1).setMergeRow(size - 1).setStartRow(c));

        BigDecimal sum = BigDecimal.ZERO;
        for (String key : supportChannelList) {
            BigDecimal value = map.get(key);
            sum = BigDecimalUtils.add(value, sum);
            BaseRowData r = new BaseRowData(key, 2).setStartRow(c);
            rowDataList.add(r);
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(value), 3).setStartRow(c));
            c++;
        }

        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 4).setMergeRow(size - 1).setStartRow(3));
        rowDataList.add(new BaseRowData("总计", 1).setStartRow(3 + size));;
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 2).setStartRow(3 + size));

        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    public static void w3(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波销售走非宁波渠道货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 宁波销售走非宁波渠道货量统计表", 1, 5).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("入库渠道", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("客户名称", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", 5).setStartRow(2));
        rowDataList.add(new BaseRowData("总计", 6).setStartRow(2));

        Map<String, List<CargoData>> map = dataList.stream().filter(s -> UN_NB_INBOUND_CHANNEL_NAME_LIST.contains(s.getInboundChannelName()))
                .collect(Collectors.groupingBy(CargoData::getInboundChannelName));

        int c = 3;
        BigDecimal sum = BigDecimal.ZERO;
        for (Map.Entry<String, List<CargoData>> e : map.entrySet()) {
            List<CargoData> cargoDataList = e.getValue();
            List<CustomerRowData> customerRowDataList = convert(cargoDataList);
            BigDecimal value = customerRowDataList.stream().map(CustomerRowData::getVolume).collect(BigDecimalCollector.toSum());
            sum = BigDecimalUtils.add(value, sum);
            String key = e.getKey();
            rowDataList.add(new BaseRowData("", 1).setStartRow(c));
            rowDataList.add(new BaseRowData(key, 2).setMergeRow(customerRowDataList.size() - 1).setStartRow(c));
            for (CustomerRowData cargoData : customerRowDataList) {
                rowDataList.add(new BaseRowData(cargoData.getSales(), 3).setStartRow(c));
                rowDataList.add(new BaseRowData(cargoData.getCustomerName(), 4).setStartRow(c));
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(cargoData.getVolume()), 5).setStartRow(c));
                c++;
            }
        }
        rowDataList.add(new BaseRowData("普船", 1).setStartRow(2).setMergeRow(c - 3));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 6).setMergeRow(c - 3 - 1).setStartRow(3));
        rowDataList.add(new BaseRowData("总计", 1).setStartRow(c));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 4).setStartRow(c));

        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    public static List<CustomerRowData> convert(List<CargoData> cargoDataList) {
        return cargoDataList.stream()
                .collect(Collectors.groupingBy(s -> s.getCustomerName() + "," + s.getSales(), Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())))
                .entrySet().stream().map(e -> {
                    List<String> keyList = Splitter.on(",").splitToList(e.getKey());
                    return new CustomerRowData(keyList.get(0), keyList.get(1), e.getValue());
                }).sorted().collect(Collectors.toList());
    }

    public static void w4(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波美东自拼-销售各仓货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 宁波美东自拼 入库数据销售各仓货量统计表", 1, 5).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("销售", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("FBA", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("沃尔玛", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("私人地址", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("总计", 5).setStartRow(2));
        rowDataList.add(new BaseRowData("总货量占比", 6).setStartRow(2));
        List<String> supportChannelList = getSupportChannelList();

        Map<String, List<WarehouseData>> map = dataList.stream()
                //.filter(s -> SUPPORT_INBOUND_CHANNEL_NAME_LIST.contains(s.getInboundChannelName()))
                .filter(s -> StringUtils.isNotBlank(s.getInboundChannelName()))
                .filter(s -> supportChannelList.contains(s.getInboundChannelName()))
                //.filter(s -> {
                //    String inboundChannelName = s.getInboundChannelName();
                //    for (String string : ALL_INBOUND_CHANNEL_NAME_LIST) {
                //        if (inboundChannelName.contains(string)) {
                //            return true;
                //        }
                //    }
                //    System.out.println(inboundChannelName);
                //    return false;
                //})
                //.filter(s -> s.getInboundChannelName().contains("宁波") && !s.getInboundChannelName().contains("九方"))
                .peek(s -> {
                    if (StringUtils.isNotBlank(s.getWarehouse()) && s.getWarehouse().contains("Walmart")) {
                        s.setArea(s.getWarehouse());
                    }
                })
                .map(c -> {
                    WarehouseData warehouseData = new WarehouseData();
                    String dept = c.getDept();
                    if (dept.contains("宁波分公司")) {
                        warehouseData.setSaleDept("宁波销售");
                    } else {
                        warehouseData.setSaleDept("深圳销售+渠道");
                    }

                    String warehouse = c.getArea();
                    if (StringUtils.isBlank(warehouse) || SRDZ_LIST.contains(warehouse)) {
                        warehouseData.setWarehouse("SRDZ");
                    } else if (FBA_P.matcher(warehouse).matches()) {
                        warehouseData.setWarehouse("FBA");
                    } else if (warehouse.contains("Walmart")) {
                        warehouseData.setWarehouse("WRM");
                    } else {
                        System.out.println("未命中:" + c);
                    }
                    warehouseData.setCustomerVolume(c.getCustomerVolume());
                    warehouseData.setVolume(c.getVolume());
                    warehouseData.setArea(c.getArea());
                    return warehouseData;
                }).collect(Collectors.groupingBy(WarehouseData::getSaleDept));

        int c = 3;
        BigDecimal fbaSum = BigDecimal.ZERO;
        BigDecimal wrmSum = BigDecimal.ZERO;
        BigDecimal srdzSum = BigDecimal.ZERO;
        BigDecimal sum = map.values().stream().flatMap(Collection::stream)
                .map(WarehouseData::getVolume).collect(BigDecimalCollector.toSum());
        if (!BigDecimalUtils.eqZero(sum)) {
            for (String key : QD_LIST) {
                List<WarehouseData> cargoDataList = map.get(key);
                Map<String, BigDecimal> wMap =
                        cargoDataList.stream().filter(s -> !StringUtils.isBlank(s.getWarehouse()))
                                .collect(Collectors.groupingBy(WarehouseData::getWarehouse,
                                        Collectors.mapping(WarehouseData::getVolume, BigDecimalCollector.toSum())));

                rowDataList.add(new BaseRowData(key, 1).setStartRow(c));
                BaseRowData fba = new BaseRowData("//", 2).setStartRow(c);
                rowDataList.add(fba);
                BigDecimal amount = BigDecimal.ZERO;
                if (wMap.containsKey("FBA")) {
                    BigDecimal fba1 = wMap.get("FBA");
                    fba.setName(BigDecimalUtils.toPlainString(fba1));
                    amount = BigDecimalUtils.add(amount, fba1);
                    fbaSum = BigDecimalUtils.add(fbaSum, fba1);
                }
                BaseRowData wrm = new BaseRowData("//", 3).setStartRow(c);
                rowDataList.add(wrm);
                if (wMap.containsKey("WRM")) {
                    BigDecimal wrm1 = wMap.get("WRM");
                    wrm.setName(BigDecimalUtils.toPlainString(wrm1));
                    amount = BigDecimalUtils.add(amount, wrm1);
                    wrmSum = BigDecimalUtils.add(wrm1, wrmSum);
                }
                BaseRowData srdz = new BaseRowData("//", 4).setStartRow(c);
                rowDataList.add(srdz);
                if (wMap.containsKey("SRDZ")) {
                    BigDecimal srdz1 = wMap.get("SRDZ");
                    srdz.setName(BigDecimalUtils.toPlainString(srdz1));
                    amount = BigDecimalUtils.add(amount, srdz1);
                    srdzSum = BigDecimalUtils.add(srdz1, srdzSum);
                }
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(amount), 5).setStartRow(c));

                BigDecimal bigDecimal = BigDecimalUtils.divKeep8Scale(amount, sum);
                bigDecimal = BigDecimalUtils.mul(bigDecimal, new BigDecimal("100"));
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(bigDecimal) + "%", 6).setStartRow(c));
                c++;
            }


            rowDataList.add(new BaseRowData("总计", 1).setStartRow(c));

            BaseRowData fbaAmount = new BaseRowData("//", 2).setStartRow(c);
            if (!BigDecimalUtils.eqZero(fbaSum)) {
                fbaAmount.setName(BigDecimalUtils.toPlainString(fbaSum));
            }
            BaseRowData wrmAmount = new BaseRowData("//", 3).setStartRow(c);
            if (!BigDecimalUtils.eqZero(wrmSum)) {
                wrmAmount.setName(BigDecimalUtils.toPlainString(wrmSum));
            }
            BaseRowData srdzAmount = new BaseRowData("//", 4).setStartRow(c);
            if (!BigDecimalUtils.eqZero(srdzSum)) {
                srdzAmount.setName(BigDecimalUtils.toPlainString(srdzSum));
            }
            BaseRowData allAmount = new BaseRowData("//", 5).setStartRow(c);
            if (!BigDecimalUtils.eqZero(sum)) {
                allAmount.setName(BigDecimalUtils.toPlainString(sum));
            }
            BaseRowData black = new BaseRowData("", 6).setStartRow(c);
            rowDataList.addAll(Arrays.asList(fbaAmount, wrmAmount, srdzAmount, allAmount, black));
        }
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    private static WarehouseData convert(CargoData c) {
        WarehouseData warehouseData = new WarehouseData();
        String dept = c.getDept();
        if (dept.contains("宁波分公司")) {
            warehouseData.setSaleDept("宁波销售");
        } else {
            warehouseData.setSaleDept("深圳销售+渠道");
        }
        String warehouse = c.getWarehouse();
        if (StringUtils.isBlank(warehouse)) {
            warehouseData.setWarehouse("SRDZ");
        }
         else if ( FBA_P.matcher(warehouse).matches()) {
            warehouseData.setWarehouse("FBA");
        } else if (warehouse.contains("Walmart")) {
            warehouseData.setWarehouse("WRM");
        } else if (isJiufang(warehouse)) {
            warehouseData.setWarehouse("九方海外仓");
        }
        warehouseData.setWeight(c.getVolume());
        warehouseData.setArea(c.getArea());
        return warehouseData;
    }



    public static void w5(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波美东自拼-各目的港数据表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 自拼入库各目的港数据表", 1, 3).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("目的港", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("FBA", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("沃尔玛仓库", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("私人地址", 4).setStartRow(2));

        List<String> supportChannelList = getSupportChannelList();
        Map<String, List<WarehouseData>> map = dataList.stream()
                .filter(c -> supportChannelList.contains(c.getOutboundChannelName()))
                .map(c -> {
                    WarehouseData warehouseData = convert(c);
                    String outboundChannelName = c.getOutboundChannelName();
                    if (getNYCList().contains(outboundChannelName)) {
                        warehouseData.setDestPort("NYC(NEW YORK)");
                    } else if(getCHIList().contains(outboundChannelName)) {
                        warehouseData.setDestPort("CHICAGO(CHICAGO)");
                    } else if (getHOUList().contains(outboundChannelName)) {
                        warehouseData.setDestPort("HOU(HOUSTON)");
                    }
                    return warehouseData;

                }).collect(Collectors.groupingBy(WarehouseData::getDestPort));

        int c = 3;
        BigDecimal fbaSum = BigDecimal.ZERO;
        BigDecimal wrmSum = BigDecimal.ZERO;
        BigDecimal srdzSum = BigDecimal.ZERO;
        BigDecimal sum = map.values().stream().flatMap(Collection::stream)
                .map(WarehouseData::getWeight).collect(BigDecimalCollector.toSum());
        if (!BigDecimalUtils.eqZero(sum)) {
            for (Map.Entry<String, List<WarehouseData>> e : map.entrySet()) {
                List<WarehouseData> cargoDataList = e.getValue();
                Map<String, BigDecimal> wMap =
                        cargoDataList.stream().filter(s -> !StringUtils.isBlank(s.getWarehouse()))
                                .collect(Collectors.groupingBy(WarehouseData::getWarehouse,
                                        Collectors.mapping(WarehouseData::getWeight, BigDecimalCollector.toSum())));

                String key = e.getKey();
                rowDataList.add(new BaseRowData(key, 1).setStartRow(c));
                BaseRowData fba = new BaseRowData("//", 2).setStartRow(c);
                rowDataList.add(fba);
                if (wMap.containsKey("FBA")) {
                    BigDecimal fba1 = wMap.get("FBA");
                    fba.setName(BigDecimalUtils.toPlainString(fba1));
                    fbaSum = BigDecimalUtils.add(fbaSum, fba1);
                }
                BaseRowData wrm = new BaseRowData("//", 3).setStartRow(c);
                rowDataList.add(wrm);
                if (wMap.containsKey("WRM")) {
                    BigDecimal wrm1 = wMap.get("WRM");
                    wrm.setName(BigDecimalUtils.toPlainString(wrm1));
                    wrmSum = BigDecimalUtils.add(wrm1, wrmSum);
                }
                BaseRowData srdz = new BaseRowData("//", 4).setStartRow(c);
                rowDataList.add(srdz);
                if (wMap.containsKey("SRDZ")) {
                    BigDecimal srdz1 = wMap.get("SRDZ");
                    srdz.setName(BigDecimalUtils.toPlainString(srdz1));
                    srdzSum = BigDecimalUtils.add(srdz1, srdzSum);
                }
                c++;
            }
            rowDataList.add(new BaseRowData("求和", 1).setStartRow(c));

            BaseRowData fbaAmount = new BaseRowData("//", 2).setStartRow(c);
            if (!BigDecimalUtils.eqZero(fbaSum)) {
                fbaAmount.setName(BigDecimalUtils.toPlainString(fbaSum));
            }
            BaseRowData wrmAmount = new BaseRowData("//", 3).setStartRow(c);
            if (!BigDecimalUtils.eqZero(wrmSum)) {
                wrmAmount.setName(BigDecimalUtils.toPlainString(wrmSum));
            }
            BaseRowData srdzAmount = new BaseRowData("//", 4).setStartRow(c);
            if (!BigDecimalUtils.eqZero(srdzSum)) {
                srdzAmount.setName(BigDecimalUtils.toPlainString(srdzSum));
            }
            rowDataList.addAll(Arrays.asList(fbaAmount, wrmAmount, srdzAmount));

            rowDataList.add(new BaseRowData("总计", 1).setStartRow(c+1));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 2).setStartRow(c + 1));

        }
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }



    public static void w6(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波美东渠道平均计费重统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData("宁波美东渠道平均计费重统计表", 1, 5).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("柜量40HQ", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("实际体积", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("实重", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("计费体积", 5).setStartRow(2));
        rowDataList.add(new BaseRowData("计费重", 6).setStartRow(2));

        List<String> supportChannelList = getSupportChannelList();
        Map<String, List<DestportData>> map = dataList.stream()
                .filter(c -> supportChannelList.contains(c.getOutboundChannelName()))
                .map(c -> {
                    DestportData warehouseData = new DestportData();
                    String outboundChannelName = c.getOutboundChannelName();
                    if (getNYCList().contains(outboundChannelName)) {
                        warehouseData.setDestPort("NYC拆");
                    } else if(getCHIList().contains(outboundChannelName)) {
                        warehouseData.setDestPort("CHI拆");
                    } else if (getHOUList().contains(outboundChannelName)) {
                        warehouseData.setDestPort("HOU拆");
                    }
                    warehouseData.setTeu(c.getTeu());
                    BigDecimal actualVolume = c.getVolume();
                    warehouseData.setActualVolume(actualVolume);
                    BigDecimal actualWeight = c.getActualWeight();
                    warehouseData.setActualWeight(actualWeight);

                    warehouseData.setWeight(BigDecimalUtils.max(BigDecimalUtils.mul(actualVolume, new BigDecimal("167")),actualWeight));
                    warehouseData.setVolume(BigDecimalUtils.max(BigDecimalUtils.div(actualWeight, new BigDecimal("363")), actualVolume));

                    warehouseData.setTeu(c.getTeu());

                    return warehouseData;

                }).collect(Collectors.groupingBy(DestportData::getDestPort));

        int c = 3;
        BigDecimal teuSum = BigDecimal.ZERO;
        BigDecimal actualWeightSum = BigDecimal.ZERO;
        BigDecimal actualVolumeSum = BigDecimal.ZERO;
        BigDecimal volumeSum = BigDecimal.ZERO;
        BigDecimal weightSum = BigDecimal.ZERO;

        for (Map.Entry<String, List<DestportData>> e : map.entrySet()) {
            List<DestportData> cargoDataList = e.getValue();
            String key = e.getKey();
            rowDataList.add(new BaseRowData(key, 1).setStartRow(c));

            BigDecimal teuAmount = BigDecimal.ZERO;
            BigDecimal actualVolumeAmount = BigDecimal.ZERO;
            BigDecimal actualWeightAmount = BigDecimal.ZERO;
            BigDecimal volumeAmount = BigDecimal.ZERO;
            BigDecimal weightAmount = BigDecimal.ZERO;
            for (DestportData destportData : cargoDataList) {
                teuAmount = BigDecimalUtils.add(teuAmount, BigDecimalUtils.toBigDecimal(destportData.getTeu()));
                actualVolumeAmount = BigDecimalUtils.add(actualVolumeAmount, destportData.getActualVolume());
                actualWeightAmount = BigDecimalUtils.add(actualWeightAmount, destportData.getActualWeight());
                volumeAmount = BigDecimalUtils.add(volumeAmount, destportData.getVolume());
                weightAmount = BigDecimalUtils.add(weightAmount, destportData.getWeight());
            }
            teuSum = BigDecimalUtils.add(teuSum, teuAmount);
            actualWeightSum = BigDecimalUtils.add(actualWeightSum, actualWeightAmount);
            actualVolumeSum = BigDecimalUtils.add(actualVolumeSum, actualVolumeAmount);
            volumeSum = BigDecimalUtils.add(volumeSum, volumeAmount);
            weightSum = BigDecimalUtils.add(weightSum, weightAmount);

            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(teuAmount), 2).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(actualVolumeAmount), 3).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(actualWeightAmount), 4).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(volumeAmount), 5).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(weightAmount), 6).setStartRow(c));

            c++;
        }
        rowDataList.add(new BaseRowData("平均值", 1).setStartRow(c));
        BigDecimal size = new BigDecimal(map.keySet().size());
        if (!BigDecimalUtils.equalsZero(size)) {
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(teuSum, size)), 2).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(actualVolumeSum, size)), 3).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(actualWeightSum, size)), 4).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(volumeSum, size)), 5).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(weightSum, size)), 6).setStartRow(c));
        }

        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    private static String getDate(List<CargoData> dataList) {
        List<Long> list = dataList.stream().filter(c -> c.getDateTime() != null)
                .map(s -> s.getDateTime().getTime()).distinct().sorted().toList();
        if (CollectionUtils.isEmpty(list) || list.size() < 2) {
            return "";
        }
        Long start = list.get(0);
        Long end = list.get(list.size() - 1);
        String startStr = DateUtil.format(new Date(start), "MM.dd");
        String endStr = DateUtil.format(new Date(end), "MM.dd");
        return startStr + "-" + endStr;
    }


    private static List<String> jiufangCangkuList = Arrays.asList(
            "USLAX", "USNBS");

    public static void zpmdg(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("自拼入库各目的港数据表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 自拼入库各目的港数据表", 1, 4).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("目的港", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("FBA", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("九方海外仓", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("沃尔玛仓库", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("私人地址", 5).setStartRow(2));


        List<ChannelDO> channelDOS = getPCChannelList();
        Map<String, ChannelDO> channelMap = channelDOS.stream().collect(Collectors.toMap(ChannelDO::getName, Function.identity()));
        Map<String, List<WarehouseData>> map = dataList.stream()
                .filter(c -> channelMap.containsKey(c.getInboundChannelName()) || DEST_MAP.containsKey(c.getInboundChannelName()))
                .map(c -> {
                    String outboundChannelName = c.getInboundChannelName();
                    ChannelDO channelDO = channelMap.get(outboundChannelName);
                    String type = channelDO.getType();
                    ChannelEnum channelEnum = EnumUtils.getEnum(ChannelEnum.class, type, ChannelEnum.DEFAULT);
                    if (channelEnum == null) {
                        return null;
                    }
                    WarehouseData warehouseData = covert2War(c);
                    W w = DEST_MAP.get(outboundChannelName);
                    warehouseData.setDestPort(channelEnum.desc());
                    if (w != null) {
                        List<String> c1 = w.getC();
                        if (c1.contains(c.getWarehouse())) {
                            warehouseData.setDestPort(EnumUtils.getEnum(ChannelEnum.class, w.t, ChannelEnum.DEFAULT).desc());
                        }
                    }
                    return warehouseData;
                })
                .filter(Objects::nonNull)
                .collect(Collectors.groupingBy(WarehouseData::getDestPort));

        int c = 3;
        BigDecimal fbaSum = BigDecimal.ZERO;
        BigDecimal wrmSum = BigDecimal.ZERO;
        BigDecimal srdzSum = BigDecimal.ZERO;
        BigDecimal jiufangSum = BigDecimal.ZERO;
        BigDecimal sum = map.values().stream().flatMap(Collection::stream)
                .map(WarehouseData::getWeight).collect(BigDecimalCollector.toSum());
        if (!BigDecimalUtils.eqZero(sum)) {
            List<String> typeList = channelDOS.stream()
                    .map(a -> EnumUtils.getEnum(ChannelEnum.class, a.getType(), ChannelEnum.DEFAULT).desc()).distinct()
                    .toList();
            for (String t : typeList) {
                List<WarehouseData> cargoDataList = map.get(t);
                Map<String, BigDecimal> wMap =
                        CollectionUtils.emptyIfNull(cargoDataList).stream().filter(s -> !StringUtils.isBlank(s.getWarehouse()))
                                .collect(Collectors.groupingBy(WarehouseData::getWarehouse,
                                        Collectors.mapping(WarehouseData::getWeight, BigDecimalCollector.toSum())));

                rowDataList.add(new BaseRowData(t, 1).setStartRow(c));
                BaseRowData fba = new BaseRowData("//", 2).setStartRow(c);
                rowDataList.add(fba);
                if (wMap.containsKey("FBA")) {
                    BigDecimal fba1 = wMap.get("FBA");
                    fba.setName(BigDecimalUtils.toPlainString(fba1));
                    fbaSum = BigDecimalUtils.add(fbaSum, fba1);
                }
                BaseRowData jiufangRow = new BaseRowData("//", 3).setStartRow(c);
                rowDataList.add(jiufangRow);
                if (wMap.containsKey("九方海外仓")) {
                    BigDecimal fba1 = wMap.get("九方海外仓");
                    jiufangRow.setName(BigDecimalUtils.toPlainString(fba1));
                    jiufangSum = BigDecimalUtils.add(jiufangSum, fba1);
                }
                BaseRowData wrm = new BaseRowData("//", 4).setStartRow(c);
                rowDataList.add(wrm);
                if (wMap.containsKey("WRM")) {
                    BigDecimal wrm1 = wMap.get("WRM");
                    wrm.setName(BigDecimalUtils.toPlainString(wrm1));
                    wrmSum = BigDecimalUtils.add(wrm1, wrmSum);
                }
                BaseRowData srdz = new BaseRowData("//", 5).setStartRow(c);
                rowDataList.add(srdz);
                if (wMap.containsKey("SRDZ")) {
                    BigDecimal srdz1 = wMap.get("SRDZ");
                    srdz.setName(BigDecimalUtils.toPlainString(srdz1));
                    srdzSum = BigDecimalUtils.add(srdz1, srdzSum);
                }
                c++;
            }

            rowDataList.add(new BaseRowData("求和", 1).setStartRow(c));

            BaseRowData fbaAmount = new BaseRowData("//", 2).setStartRow(c);
            if (!BigDecimalUtils.eqZero(fbaSum)) {
                fbaAmount.setName(BigDecimalUtils.toPlainString(fbaSum));
            }
            BaseRowData jiufangAmount = new BaseRowData("//", 3).setStartRow(c);
            if (!BigDecimalUtils.eqZero(jiufangSum)) {
                jiufangAmount.setName(BigDecimalUtils.toPlainString(jiufangSum));
            }
            BaseRowData wrmAmount = new BaseRowData("//", 4).setStartRow(c);
            if (!BigDecimalUtils.eqZero(wrmSum)) {
                wrmAmount.setName(BigDecimalUtils.toPlainString(wrmSum));
            }

            BaseRowData srdzAmount = new BaseRowData("//", 5).setStartRow(c);
            if (!BigDecimalUtils.eqZero(srdzSum)) {
                srdzAmount.setName(BigDecimalUtils.toPlainString(srdzSum));
            }
            rowDataList.addAll(Arrays.asList(fbaAmount, wrmAmount, srdzAmount,jiufangAmount));

            rowDataList.add(new BaseRowData("总计", 1).setStartRow(c + 1));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 3).setStartRow(c + 1));

        }
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    private static boolean isJiufang(String w) {
        for (String s : jiufangCangkuList) {
            if (w.contains(s)) {
                return true;
            }
        }
        return false;
    }


    public static void xiaoshougecang(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波美线自拼-销售各仓货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 宁波美线自拼 入库数据销售各仓货量统计表", 1, 6).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("销售", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("FBA", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("沃尔玛", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("九方海外仓", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("私人地址", 5).setStartRow(2));
        rowDataList.add(new BaseRowData("总计", 6).setStartRow(2));
        rowDataList.add(new BaseRowData("总货量占比", 7).setStartRow(2));
        List<String> supportChannelList = getPCSupportChannelList();
        Map<String, List<WarehouseData>> map = dataList.stream()
                .filter(s -> StringUtils.isNotBlank(s.getInboundChannelName()))
                .filter(s -> supportChannelList.contains(s.getInboundChannelName()))
                .peek(s -> {
                    if (StringUtils.isNotBlank(s.getWarehouse()) && s.getWarehouse().contains("Walmart")) {
                        s.setArea(s.getWarehouse());
                    }
                })
                .map(c -> {
                    return covert2War(c);
                }).collect(Collectors.groupingBy(WarehouseData::getSaleDept));
        int c = 3;
        BigDecimal fbaSum = BigDecimal.ZERO;
        BigDecimal wrmSum = BigDecimal.ZERO;
        BigDecimal srdzSum = BigDecimal.ZERO;
        BigDecimal jiufangSum = BigDecimal.ZERO;
        BigDecimal sum = map.values().stream().flatMap(Collection::stream)
                .map(WarehouseData::getVolume).collect(BigDecimalCollector.toSum());
        if (!BigDecimalUtils.eqZero(sum)) {
            for (String key : QD_LIST) {
                List<WarehouseData> cargoDataList = map.get(key);
                Map<String, BigDecimal> wMap =
                        cargoDataList.stream().filter(s -> !StringUtils.isBlank(s.getWarehouse()))
                                .collect(Collectors.groupingBy(WarehouseData::getWarehouse,
                                        Collectors.mapping(WarehouseData::getVolume, BigDecimalCollector.toSum())));

                rowDataList.add(new BaseRowData(key, 1).setStartRow(c));
                BaseRowData fba = new BaseRowData("//", 2).setStartRow(c);
                rowDataList.add(fba);
                BigDecimal amount = BigDecimal.ZERO;
                if (wMap.containsKey("FBA")) {
                    BigDecimal fba1 = wMap.get("FBA");
                    fba.setName(BigDecimalUtils.toPlainString(fba1));
                    amount = BigDecimalUtils.add(amount, fba1);
                    fbaSum = BigDecimalUtils.add(fbaSum, fba1);
                }
                BaseRowData wrm = new BaseRowData("//", 3).setStartRow(c);
                rowDataList.add(wrm);
                if (wMap.containsKey("WRM")) {
                    BigDecimal wrm1 = wMap.get("WRM");
                    wrm.setName(BigDecimalUtils.toPlainString(wrm1));
                    amount = BigDecimalUtils.add(amount, wrm1);
                    wrmSum = BigDecimalUtils.add(wrm1, wrmSum);
                }
                BaseRowData jiufangRow = new BaseRowData("//", 4).setStartRow(c);
                rowDataList.add(jiufangRow);
                if (wMap.containsKey("九方海外仓")) {
                    BigDecimal fba1 = wMap.get("九方海外仓");
                    jiufangRow.setName(BigDecimalUtils.toPlainString(fba1));
                    amount = BigDecimalUtils.add(amount, fba1);
                    jiufangSum = BigDecimalUtils.add(jiufangSum, fba1);
                }
                BaseRowData srdz = new BaseRowData("//", 5).setStartRow(c);
                rowDataList.add(srdz);
                if (wMap.containsKey("SRDZ")) {
                    BigDecimal srdz1 = wMap.get("SRDZ");
                    srdz.setName(BigDecimalUtils.toPlainString(srdz1));
                    amount = BigDecimalUtils.add(amount, srdz1);
                    srdzSum = BigDecimalUtils.add(srdz1, srdzSum);
                }
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(amount), 6).setStartRow(c));

                BigDecimal bigDecimal = BigDecimalUtils.divKeep8Scale(amount, sum);
                bigDecimal = BigDecimalUtils.mul(bigDecimal, new BigDecimal("100"));
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(bigDecimal) + "%", 7).setStartRow(c));
                c++;
            }


            rowDataList.add(new BaseRowData("总计", 1).setStartRow(c));

            BaseRowData fbaAmount = new BaseRowData("//", 2).setStartRow(c);
            if (!BigDecimalUtils.eqZero(fbaSum)) {
                fbaAmount.setName(BigDecimalUtils.toPlainString(fbaSum));
            }
            BaseRowData wrmAmount = new BaseRowData("//", 3).setStartRow(c);
            if (!BigDecimalUtils.eqZero(wrmSum)) {
                wrmAmount.setName(BigDecimalUtils.toPlainString(wrmSum));
            }
            BaseRowData jiufangAmount = new BaseRowData("//", 4).setStartRow(c);
            if (!BigDecimalUtils.eqZero(jiufangSum)) {
                jiufangAmount.setName(BigDecimalUtils.toPlainString(jiufangSum));
            }
            BaseRowData srdzAmount = new BaseRowData("//", 5).setStartRow(c);
            if (!BigDecimalUtils.eqZero(srdzSum)) {
                srdzAmount.setName(BigDecimalUtils.toPlainString(srdzSum));
            }
            BaseRowData allAmount = new BaseRowData("//", 6).setStartRow(c);
            if (!BigDecimalUtils.eqZero(sum)) {
                allAmount.setName(BigDecimalUtils.toPlainString(sum));
            }
            BaseRowData black = new BaseRowData("", 7).setStartRow(c);
            rowDataList.addAll(Arrays.asList(fbaAmount, wrmAmount, srdzAmount, allAmount, black,jiufangAmount));
        }
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    private static WarehouseData covert2War(CargoData c) {
        WarehouseData warehouseData = new WarehouseData();
        String dept = c.getDept();
        if (dept.contains("宁波")) {
            warehouseData.setSaleDept("宁波销售");
        } else {
            warehouseData.setSaleDept("深圳销售+渠道");
        }
        String warehouse = c.getWarehouse();
        if (StringUtils.isNotBlank(warehouse)) {
            if (FBA_P.matcher(warehouse).matches() || "IUSL".equals(warehouse) || "IUSJ".equals(warehouse)||"IUSQ".equals(warehouse)) {
                warehouseData.setWarehouse("FBA");
            } else if (warehouse.contains("Walmart")) {
                warehouseData.setWarehouse("WRM");
            } else if (isJiufang(warehouse)) {
                warehouseData.setWarehouse("九方海外仓");
            }
            else {
                warehouseData.setWarehouse("SRDZ");
            }
        }else {
            warehouseData.setWarehouse("SRDZ");
        }
        warehouseData.setCustomerVolume(c.getCustomerVolume());
        warehouseData.setVolume(c.getVolume());
        warehouseData.setWeight(c.getVolume());
        warehouseData.setArea(c.getArea());
        return warehouseData;
    }

    private static List<String> kuaichuanList = Arrays.asList("准时达卡派(包税)", "准时达", "美森");

    public static void feiningbo(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波销售走非宁波渠道货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 宁波销售走非宁波渠道货量统计表", 1, 5).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("入库渠道", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("客户名称", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", 5).setStartRow(2));
        rowDataList.add(new BaseRowData("总计", 6).setStartRow(2));

        List<String> supportChannelList = getSupportChannelList();
        Map<Boolean, Map<String, List<CargoData>>> allMap = dataList.stream()
                .filter(s -> StringUtils.isNotBlank(s.getDept()))
                .filter(s -> s.getDept().contains("宁波"))
                .filter(s -> !supportChannelList.contains(s.getInboundChannelName()))
                .collect(Collectors.groupingBy(c -> {
                            String inboundChannelName = c.getInboundChannelName();
                            for (String s : kuaichuanList) {
                                if (inboundChannelName.contains(s)) {
                                    return true;
                                }
                            }
                            return false;
                        },
                        Collectors.mapping(Function.identity(), Collectors.groupingBy(CargoData::getInboundChannelName))));

        int c = 3;
        BigDecimal sum = BigDecimal.ZERO;
        int mergeSize = 0;
        Map<String, List<CargoData>> map = allMap.getOrDefault(false, Collections.emptyMap());
        for (Map.Entry<String, List<CargoData>> e : map.entrySet()) {
            List<CargoData> cargoDataList = e.getValue();
            List<CustomerRowData> customerRowDataList = convert(cargoDataList);
            BigDecimal value = customerRowDataList.stream().map(CustomerRowData::getVolume).collect(BigDecimalCollector.toSum());
            sum = BigDecimalUtils.add(value, sum);
            String key = e.getKey();
            mergeSize += customerRowDataList.size();
            rowDataList.add(new BaseRowData("", 1).setStartRow(c));
            rowDataList.add(new BaseRowData(key, 2).setMergeRow(customerRowDataList.size() - 1).setStartRow(c));
            for (CustomerRowData cargoData : customerRowDataList) {
                rowDataList.add(new BaseRowData(cargoData.getSales(), 3).setStartRow(c));
                rowDataList.add(new BaseRowData(cargoData.getCustomerName(), 4).setStartRow(c));
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(cargoData.getVolume()), 5).setStartRow(c));
                c++;
            }
        }
        rowDataList.add(new BaseRowData("普船", 1).setStartRow(3).setMergeRow(mergeSize - 1));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 6).setMergeRow(mergeSize - 1).setStartRow(3));
        Map<String, List<CargoData>> kuaiMap = allMap.get(true);
        if (MapUtils.isNotEmpty(kuaiMap)) {
            int startRow = c;
            mergeSize = 0;
            BigDecimal kuaiSum = BigDecimal.ZERO;
            for (Map.Entry<String, List<CargoData>> e : kuaiMap.entrySet()) {
                List<CargoData> cargoDataList = e.getValue();
                List<CustomerRowData> customerRowDataList = convert(cargoDataList);
                mergeSize += customerRowDataList.size();
                BigDecimal value = customerRowDataList.stream().map(CustomerRowData::getVolume).collect(BigDecimalCollector.toSum());
                kuaiSum = BigDecimalUtils.add(value, kuaiSum);
                String key = e.getKey();
                rowDataList.add(new BaseRowData("", 1).setStartRow(c));
                rowDataList.add(new BaseRowData(key, 2).setMergeRow(customerRowDataList.size() - 1).setStartRow(c));
                for (CustomerRowData cargoData : customerRowDataList) {
                    rowDataList.add(new BaseRowData(cargoData.getSales(), 3).setStartRow(c));
                    rowDataList.add(new BaseRowData(cargoData.getCustomerName(), 4).setStartRow(c));
                    rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(cargoData.getVolume()), 5).setStartRow(c));
                    c++;
                }
            }
            sum = BigDecimalUtils.add(kuaiSum, sum);
            rowDataList.add(new BaseRowData("快船", 1).setStartRow(startRow).setMergeRow(mergeSize - 1));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(kuaiSum), 6).setMergeRow(mergeSize - 1).setStartRow(startRow));

        }
        rowDataList.add(new BaseRowData("总计", 1).setStartRow(c));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 4).setStartRow(c));

        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    public static void gequdao(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList, String date) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("美线各渠道货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData(date + " 美线各渠道货量统计表", 1, 3).setCellStyle(centerStyle(xssfWorkbook)));
        rowDataList.add(new BaseRowData("", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("入库渠道", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("总计（CBM）", 4).setStartRow(2));
        QueryWrapper<ChannelDO> queryWrapper = new QueryWrapper<>();
        List<ChannelDO> channelDOS = SpringContextUtils.getBean(ChannelDOMapper.class).selectList(queryWrapper);
        List<String> supportChannelList = channelDOS.stream().map(ChannelDO::getName).toList();
        Map<Boolean, List<ChannelDO>> cMap = channelDOS.stream().collect(Collectors.groupingBy(ChannelDO::getIsOrdinaryVessel));
        Map<String, BigDecimal> map = dataList.stream().filter(s -> supportChannelList.contains(s.getInboundChannelName()))
                .collect(Collectors.groupingBy(CargoData::getInboundChannelName, Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())));

        int c = 3;
        int size = supportChannelList.size();

        BigDecimal sum = BigDecimal.ZERO;
        List<ChannelDO> commonList = cMap.get(true);
        rowDataList.add(new BaseRowData("普船", 1).setMergeRow(commonList.size() - 1).setStartRow(c));
        // 普船
        for (ChannelDO channelDO : commonList) {
            String key = channelDO.getName();
            BigDecimal value = map.get(key);
            sum = BigDecimalUtils.add(value, sum);
            BaseRowData r = new BaseRowData(key, 2).setStartRow(c);
            rowDataList.add(r);
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(value), 3).setStartRow(c));
            c++;
        }
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 4).setMergeRow(commonList.size() - 1).setStartRow(3));
        // 外配
        List<ChannelDO> unCommonList = cMap.get(false);
        if (CollectionUtils.isNotEmpty(unCommonList)) {
            rowDataList.add(new BaseRowData("外配", 1).setMergeRow(unCommonList.size() - 1).setStartRow(c));
            BaseRowData s = new BaseRowData("", 4).setMergeRow(unCommonList.size() - 1).setStartRow(c);
            rowDataList.add(s);
            BigDecimal unSum = BigDecimal.ZERO;
            for (ChannelDO channelDO : unCommonList) {
                String key = channelDO.getName();
                BigDecimal value = map.get(key);
                unSum = BigDecimalUtils.add(value, unSum);
                BaseRowData r = new BaseRowData(key, 2).setStartRow(c);
                rowDataList.add(r);
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(value), 3).setStartRow(c));
                c++;
            }
            s.setName(BigDecimalUtils.toPlainString(unSum));
            sum = BigDecimalUtils.add(unSum, sum);
        }

        rowDataList.add(new BaseRowData("总计", 1).setStartRow(3 + size));;
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 2).setStartRow(3 + size));

        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }
}
